Electronic spreadsheet program modules are useful tools for helping users perform complex calculations. Many electronic spreadsheet program modules include the concept of a workbook with a number of worksheets in a workbook. An external reference, or link, from within one workbook may be made to another workbook. An external reference or link in an electronic spreadsheet may be generally described as when the value of one cell is dependent on the value of another cell in another spreadsheet. Cells may also depend on the value of a defined name or function in another worksheet. Graphic items, such as charts and text boxes, may also be linked to cells, defined names, or functions within another worksheet. Thus, generally described, a link refers to a workbook object (cell, named range, text box) whose content (value or formula) is dependent on an external source. The external source is typically another spreadsheet program but could also be a DDE server, OLE server, or anything that the spreadsheet program is capable of interpreting.
For example, links are commonly used when multiple spreadsheets with common structures are used, such as profit and loss statements for different groups within an organization (each group using their own spreadsheet). A summary spreadsheet may be created that links to values in the external spreadsheets and aggregates the data and formats the data for reporting to management. Of course, there are numerous other examples in which links can be used.
Although links are extremely useful, they can also be problematic. The client spreadsheet stores a local copy of the value of links and needs to be updated regularly to ensure that the data is up-to-date. If a link to the source workbook has not been updated, the source workbook has been moved, the source workbook has been renamed, etc., then the link may not function properly. For example, a common problem for electronic spreadsheet users is to have broken links (a link that cannot be updated) because files can no longer be found or worksheets have been renamed. For example, suppose a server is damaged and is replaced with a new server. The server would have a new name and, thus, any workbook linking to one of the files that was moved to the new server would have broken links because the workbook still believes the source files are located on the damaged server.
Because of the complex and critical calculations performed using spreadsheets, it is important for users to know whether or not the links in their workbook are valid. For example, corporations may maintain thousands of workbooks with a single workbook often having dozens or even hundreds of links. Maintaining these workbooks is often the sole job of employees. One solution to determine whether a link is valid is to attempt to update the link. Updating a link attempts to open the source workbook and any other supporting files. While updating, if the spreadsheet program module determines that a link is broken, the user is then prompted with a message for each broken link indicating that the link is broken. Although updating links works fairly well, it can quickly become burdensome if a workbook includes numerous links. For example, many corporations use workbooks to perform complex calculations and these workbooks may have links that take hours to update completely. Thus, the only way to check the status of links was to attempt to update the links and manually deal with errors on a sequential basis as the spreadsheet program module reports them. Checking status by updating links also changes the current values on the workbook. In many corporations, maintaining spreadsheets (e.g. making sure links work) is a job performed by IT or support personnel, whereas the analysis of the data is performed by other personnel. It is possible that updating links at the wrong time could interrupt the business process.
Thus, there is a need for reporting the status of links in electronic spreadsheets without updating the links so that users can quickly check the status of links.